﻿
CREATE PROCEDURE [dbo].[s_Pages_Get_Ex]
	@LoanID int = 3261,
	@ImageTypeID int = 1,
	@PageTypeID varchar(50) = 'PDF', -- PageTypeID if Numeric, ContentType if not
	@PageNumbers varchar(1024) = '' -- '1,2,5,34,...' ('' - to get whole folder)
AS

IF ISNUMERIC(@PageTypeID) = 0 SET @PageTypeID = dbo.PageTypeIDByContentType(@PageTypeID)

DECLARE @T TABLE(LoanID int,PageID int,Data image,PageNumber int,HasData varchar(50),HasData0 varchar(50),PageTypeID int,ImageTYpeID int,Pos int)

INSERT INTO @T
SELECT @LoanID AS LoanID,t_Page.ID PageID,ISNULL(t_PageStore.Data, t_PageStore0.Data) AS Data, t_Page.PageNumber, CASE WHEN t_PageStore.Data IS NULL 
               THEN 'NULL' ELSE 'Data' END AS HasData, CASE WHEN t_PageStore0.Data IS NULL THEN 'NULL' ELSE 'Data' END AS HasData0, 
               ISNULL(t_PageStore.PageTypeID, t_PageStore0.PageTypeID) AS PageTypeID,ImageTypeID,PNs.Pos
FROM  t_Image
INNER JOIN t_Page ON t_Image.ID = t_Page.ImageID
INNER JOIN GetPageNumbers(@LoanID,@ImageTypeID,@PageNumbers) PNs ON t_Page.PageNumber = PNs.PageNumber
LEFT OUTER JOIN (SELECT * FROM   t_PageStore WHERE PageTypeID = 0) t_PageStore0
		 ON t_Page.ID = t_PageStore0.PageID
LEFT OUTER JOIN (SELECT * FROM   t_PageStore WHERE PageTypeID = @PageTypeID) t_PageStore
		 ON t_Page.ID = t_PageStore.PageID
WHERE (t_Image.LoanID = @LoanID) AND
			(t_Image.ImageTypeID = @ImageTypeID)
ORDER BY PNs.Pos

SELECT TOP 100 PERCENT * FROM @T ORDER BY Pos


